package com.imist.demo;

import org.junit.Test;

import java.sql.*;

public class JDBCDemo2 {

    /**
     * 保存注意：数据库的连接需要设置字符编码，不然会导致插入中文乱码
     */
    @Test
    public void saveDemo(){
        Connection conn = null;
        Statement statement = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test?useSSL=false&characterEncoding=UTF-8","root","123456");
            statement = conn.createStatement();
            String sql = "insert user(username,password,name) values ('dddd','123456','王五')";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("保存成功");
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }finally {
           if (conn != null){
               try {
                   conn.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
               conn = null;
           }
            if (statement != null){
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                statement = null;
            }
        }
    }

    /**
     * 修改操作
     */
    @Test
    public void updateDemo(){
        Connection conn = null;
        Statement statement = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test?useSSl=true&characterEncoding=utf-8","root","123456");
            String sql = "UPDATE user SET username = '中国电信' ,password = '66666666' ,name = '小李' WHERE uid = 3";
            statement = conn.createStatement();
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("更新成功");
            }else {
                System.out.println("更新失败");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
            if (statement != null){
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                statement = null;
            }
        }
    }
    /**
     * 删除操作
     */
    @Test
    public void deleteDemo(){
        Connection conn = null;
        Statement statement = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test?useSSl=true&characterEncoding=utf-8","root","123456");
            String deleteql = "DELETE FROM user Where uid = 1 ";
            statement = conn.createStatement();
            int i = statement.executeUpdate(deleteql);
            if (i > 0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
            if (statement != null){
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                statement = null;
            }
        }
    }

    @Test
    public void selectDemo(){

        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test?useSSL=false&characterEncoding=UTF-8","root","123456");
            statement = conn.createStatement();
            //String sql = "select * FROM user";
            String sql = "select * FROM user WHERE uid = 3";//查询一条记录
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString("username") + "\t" +resultSet.getString("password")+"\t" +resultSet.getString("name"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
            if (statement != null){
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                statement = null;
            }
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                resultSet = null;
            }
        }
    }


    /**
     * 自由编程部分代码
     */
    @Test
    public void Demo2(){
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test?useSSL=false&characterEncoding=UTF-8","root","123456");
            statement = conn.createStatement();
            String insertSql = "INSERT goods(name, price, desp) VALUES\n" +
                    "  ('手机',2000.0,'黑色，32G内存'),\n" +
                    "  ('冰箱',1500.0,'银色，风冷'),\n" +
                    "  ('空调',3500.0,'白色，海尔统帅'),\n" +
                    "  ('洗衣机',1100.0,'小天鹅，滚筒洗衣机');";
            String updateSql = "UPDATE goods SET name = 'iPhone' ,price = 8888.88,desp = '苹果手机' WHERE id = 5";
            String deleteSql = "DELETE FROM goods WHERE id = 1";
            int i = statement.executeUpdate(insertSql);
            int j = statement.executeUpdate(updateSql);
            int k = statement.executeUpdate(deleteSql);
            if (i > 0 || j > 0 || k>0){
               resultSet = statement.executeQuery("select * from goods ");
               while(resultSet.next()){
                   String name = resultSet.getString("name");
                   Float price = resultSet.getFloat("price");
                   String desp = resultSet.getString("desp");
                   System.out.println(name +"   " + price +"   " +desp);
               }
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }finally {
           if (conn != null){
               try {
                   conn.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
               conn = null;
           }
            if (statement != null){
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                statement = null;
            }
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                resultSet = null;
            }
        }

    }
}
